{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "      id        date goods  sales\n0   1001  2019-01-01     A   3000\n1   1001  2019-01-01     B   2000\n2   1002  2019-01-01     A   3000\n3   1003  2019-01-01     B   2000\n4   1001  2019-01-02     A   3000\n5   1002  2019-01-02     A   3000\n6   1002  2019-01-02     B   2000\n7   1003  2019-01-02     A   3000\n8   1001  2019-01-03     A   3000\n9   1002  2019-01-03     B   2000\n10  1001  2019-01-04     A   3000\n11  1002  2019-01-04     A   3000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>date</th>\n      <th>goods</th>\n      <th>sales</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1001</td>\n      <td>2019-01-01</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1001</td>\n      <td>2019-01-01</td>\n      <td>B</td>\n      <td>2000</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1002</td>\n      <td>2019-01-01</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1003</td>\n      <td>2019-01-01</td>\n      <td>B</td>\n      <td>2000</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1001</td>\n      <td>2019-01-02</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>1002</td>\n      <td>2019-01-02</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>1002</td>\n      <td>2019-01-02</td>\n      <td>B</td>\n      <td>2000</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>1003</td>\n      <td>2019-01-02</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>1001</td>\n      <td>2019-01-03</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>1002</td>\n      <td>2019-01-03</td>\n      <td>B</td>\n      <td>2000</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>1001</td>\n      <td>2019-01-04</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>1002</td>\n      <td>2019-01-04</td>\n      <td>A</td>\n      <td>3000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 1
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "data = {'id':[1001,1001,1002,1003,1001,1002,1002,1003,1001,1002,1001,1002],\n",
    "'date':['2019-01-01','2019-01-01','2019-01-01','2019-01-01','2019-01-02','2019-01-02','2019-01-02','2019-01-02','2019-01-03','2019-01-03','2019-01-04','2019-01-04'],\n",
    "'goods':['A','B','A','B','A','A','B','A','A','B','A','A'],\n",
    "'sales':[3000,2000,3000,2000,3000,3000,2000,3000,3000,2000,3000,3000]}\n",
    "df = pd.DataFrame(data)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "                 sales\nid   date             \n1001 2019-01-01   5000\n     2019-01-02   3000\n     2019-01-03   3000\n     2019-01-04   3000\n1002 2019-01-01   3000\n     2019-01-02   5000\n     2019-01-03   2000\n     2019-01-04   3000\n1003 2019-01-01   2000\n     2019-01-02   3000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>sales</th>\n    </tr>\n    <tr>\n      <th>id</th>\n      <th>date</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">1001</th>\n      <th>2019-01-01</th>\n      <td>5000</td>\n    </tr>\n    <tr>\n      <th>2019-01-02</th>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>2019-01-03</th>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>2019-01-04</th>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">1002</th>\n      <th>2019-01-01</th>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th>2019-01-02</th>\n      <td>5000</td>\n    </tr>\n    <tr>\n      <th>2019-01-03</th>\n      <td>2000</td>\n    </tr>\n    <tr>\n      <th>2019-01-04</th>\n      <td>3000</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">1003</th>\n      <th>2019-01-01</th>\n      <td>2000</td>\n    </tr>\n    <tr>\n      <th>2019-01-02</th>\n      <td>3000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "source": [
    "#利用 pivot_table 统计该企业每个人每天销售商品 A 和 B 的金额之和\n",
    "df2=pd.pivot_table(df,index=['id','date'],values=['sales'],aggfunc=np.sum)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "           sales                                        \ndate  2019-01-01 2019-01-02 2019-01-03 2019-01-04  total\nid                                                      \n1001        5000       3000       3000       3000  14000\n1002        3000       5000       2000       3000  13000\n1003        2000       3000          0          0   5000\ntotal      10000      11000       5000       6000  32000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th colspan=\"5\" halign=\"left\">sales</th>\n    </tr>\n    <tr>\n      <th>date</th>\n      <th>2019-01-01</th>\n      <th>2019-01-02</th>\n      <th>2019-01-03</th>\n      <th>2019-01-04</th>\n      <th>total</th>\n    </tr>\n    <tr>\n      <th>id</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1001</th>\n      <td>5000</td>\n      <td>3000</td>\n      <td>3000</td>\n      <td>3000</td>\n      <td>14000</td>\n    </tr>\n    <tr>\n      <th>1002</th>\n      <td>3000</td>\n      <td>5000</td>\n      <td>2000</td>\n      <td>3000</td>\n      <td>13000</td>\n    </tr>\n    <tr>\n      <th>1003</th>\n      <td>2000</td>\n      <td>3000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>5000</td>\n    </tr>\n    <tr>\n      <th>total</th>\n      <td>10000</td>\n      <td>11000</td>\n      <td>5000</td>\n      <td>6000</td>\n      <td>32000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "#增加一列，统计出每个人的销售总金额\n",
    "pd.pivot_table(df,index=['id'],columns='date',values=['sales'],aggfunc=np.sum,fill_value=0,margins=1,margins_name='total')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "      sales\nid         \n1001  14000\n1002  13000\n1003   5000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>sales</th>\n    </tr>\n    <tr>\n      <th>id</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1001</th>\n      <td>14000</td>\n    </tr>\n    <tr>\n      <th>1002</th>\n      <td>13000</td>\n    </tr>\n    <tr>\n      <th>1003</th>\n      <td>5000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "source": [
    "pd.pivot_table(df2,index=['id'],values=['sales'],aggfunc=np.sum)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}